package extra;

import psl.CommonServices;

import java.sql.*;
import java.util.ArrayList;

/**
 * Created by IntelliJ IDEA.
 * User: phiteshkumar
 * Date: Feb 24, 2007
 * Time: 10:22:53 AM
 */
public class SQLUtil
{
    public static String msDatabaseDriver = "oracle.jdbc.driver.OracleDriver";
    public static String msDatabaseURL = "jdbc:oracle:thin:@saturn:1521:saturn";
    public static String msDatabaseUserName = "export";
    public static String msDatabasePassword = "export";
    public static Connection moConnection;

    public static Connection getConnection()
    {
        if (moConnection != null) return moConnection;
        try
        {
            Class.forName(msDatabaseDriver);
            moConnection = DriverManager.getConnection(msDatabaseURL, msDatabaseUserName, msDatabasePassword);
            moConnection.setAutoCommit(false);
        }
        catch (Exception feException)
        {
            CommonServices.throwError(feException);
        }

        return moConnection;
    }

    public static ArrayList retrieve(String fsSQL)
    {
        Connection loConnection = getConnection();

        ArrayList lvRecords = new ArrayList();
        Statement loStatement;
        ResultSet loResultSet;
        int liCols;
        int liCol;

        try
        {
            loStatement = loConnection.createStatement();
            loResultSet = loStatement.executeQuery(fsSQL);
            ResultSetMetaData loResultSetMetaData = loResultSet.getMetaData();
            liCols = loResultSetMetaData.getColumnCount();
            // Get row/column values
            while (loResultSet.next())
            {
                String[] laRecord = new String[liCols];
                for (liCol = 1; liCol <= liCols; liCol++)
                {
                    String lsValue = null;
                    Object loValue = loResultSet.getObject(liCol);
                    if (loValue != null)
                    {
                        lsValue = loValue.toString();
                    }
                    laRecord[liCol - 1] = lsValue;
                }
                lvRecords.add(laRecord);
            }
            loStatement.close();
        }
        catch (SQLException feException)
        {
            CommonServices.throwError(fsSQL, feException);
        }
        return lvRecords;
    }

    public static String executeUpdate(String fsSQL)
    {
        Connection loConnection = getConnection();
        try
        {
            Statement loStatement = loConnection.createStatement();
            loStatement.executeUpdate(fsSQL);
            loStatement.close();
            loConnection.commit();
        }
        catch (SQLException feException)
        {
            //CommonServices.throwError(fsSQL, feException);
            return feException.getMessage();
        }
        return null;
    }
    
}
